-- @owner: lihongji
-- @date: 2022-07-18
-- @testpoint: var_pop-merge into 的测试，合理报错

--step1:建表；expect：成功
drop table if exists t_ustore_var_pop_case0027_01;
create table  t_ustore_var_pop_case0027_01(col_1 integer,col_2 bigint,col_3 float8,col_4 decimal(12,6),col_5 bool,col_6 char(30),
col_7 varchar2(50),col_8 varchar(30),col_9 interval day to second,col_10 timestamp,col_11 date,
col_12 smalldatetime,col_13 timestamp without time zone, col_14 blob,col_15 clob,col_16 int[])with (storage_type=ustore);
--step2:创建序列;expect:成功
drop sequence if exists s_ustore_var_pop_case0027_01;
create sequence s_ustore_var_pop_case0027_01 increment by 1 start with 10;
--step3：清空数据；expect：成功
truncate table t_ustore_var_pop_case0027_01;
--step4：插入数据；expect：成功
begin
	for i in 1..50 loop
      insert into t_ustore_var_pop_case0027_01 values(
	  i,
      s_ustore_var_pop_case0027_01.nextval,
	  i+445.255,
	  98*0.99*i,
	  true,
	  lpad('abc','30','@'),
	  lpad('abc','30','b'),
	  rpad('abc','30','e'),
	  (interval '4 5:12:10.222' day to second(3)),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),
	  lpad('10','12','01010')::blob,
	  rpad('abc','9','a@123&^%djgk'),
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}'
	  );

    end loop;
end;
/
insert into t_ustore_var_pop_case0027_01 select * from t_ustore_var_pop_case0027_01;
insert into t_ustore_var_pop_case0027_01 select * from t_ustore_var_pop_case0027_01;
--step5：建表；expect：成功
drop table if exists t_ustore_var_pop_case0027_02;
create table t_ustore_var_pop_case0027_02 with (storage_type=ustore) as select distinct col_1,col_2,col_3,col_4,col_5 from t_ustore_var_pop_case0027_01;
--step6：进行merge into；expect：失败
merge into t_ustore_var_pop_case0027_01 t1 using t_ustore_var_pop_case0027_02 t2 on (t1.col_2=t2.col_2)
when matched then update set t1.col_1=var_pop(t2.col_1)
when not matched then insert(t1.col_2) values(2000);
merge into t_ustore_var_pop_case0027_01 t1 using t_ustore_var_pop_case0027_02 t2 on (t1.col_2!=t2.col_2)
when matched then update set t1.col_1=1000
when not matched then insert(t1.col_2) values(var_pop(t2.col_1));
--step6:清理环境；expect：成功
drop sequence if exists s_ustore_var_pop_case0027_01;
drop table if exists t_ustore_var_pop_case0027_01;
drop table if exists t_ustore_var_pop_case0027_02;
